﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows;
using ZaidimasSandelys.CustomDrawTools;
using ZaidimasSandelys.Models.DataStructures;
using ZaidimasSandelys.Models.Interfaces;

namespace ZaidimasSandelys.Models.Classes
{
    public class AppSqlCeData : IAppData
    {
        /// <summary>
        /// The application will use single connection to internal database.
        /// </summary>
        private static SqlCeConnection _conn = null;
        private static bool _isOpened = false;
        public string ConnString { get; set; }

        public DbConnection Conn
        {
            get
            {
                if (_conn == null)
                    _conn = new SqlCeConnection(ConnString);
                return _conn;
            }
        }

        public AppSqlCeData(string connString)
        {
            ConnString = connString;
            string dBasePath = GetSqlLighPath();
            AppDomain.CurrentDomain.SetData("DataDirectory", dBasePath);
        }

        public void CloseConnection()
        {
            _isOpened = false;
            _conn = null;
        }

        public IEnumerable<MainMenuItem> GetMainMenu()
        {
            const string commandText = "select * from Menu where Code='Main' order by Position;";
            DataTable menuTable = ReadAllData(commandText);

            var query = (
                from mnu in menuTable.AsEnumerable()
                select new MainMenuItem()
                {
                    Text = mnu.Field<string>("Text"),
                    Value = SafeConvertionToGameLevel(mnu.Field<short>("Value")),
                }).ToList();

            return query;
        }

        public string[] GetStartButtonText()
        {
            const string commandText = "select * from Menu where Code='bStart' order by Position;";
            DataTable menuTable = ReadAllData(commandText);

            var query = (
                    from mnu in menuTable.AsEnumerable()
                    select mnu.Field<string>("Text")
                ).ToArray();

            return query;
        }

        /// <summary>
        /// SqlCE nemoka dirbti su parametrais, todėl tenka formuoti SQLą be parametrų
        /// </summary>
        /// <param name="gameComplexity"></param>
        /// <returns></returns>
        public IEnumerable<VariantMenuItem> GetGameVariants(GameLevel gameComplexity)
        {
            const string commandText = "select * from Menu where Code='gmVrnt' and Value={0} order by Position;";
            DataTable menuTable = ReadAllData(String.Format(commandText, (short)gameComplexity));

            var query = (
                from mnu in menuTable.AsEnumerable()
                select new VariantMenuItem()
                {
                    Text = mnu.Field<string>("Text"),
                    Value = mnu.Field<short>("Position"),
                }).ToList();

            return query;
        }

        /// <summary>
        /// Verčiami į kitas kalbas tekstai
        /// </summary>
        /// <returns></returns>
        public string[] GetMultilanguageConstants(string id)
        {
            string commandText = String.Format("select * from Multilanguage where Code = N'{0}' order by Position", id);
            DataTable mltTable = ReadAllData(commandText);

            var query = (
                    from mlt in mltTable.AsEnumerable()
                    select mlt.Field<string>("Text")
                ).ToArray();
            
            return query;
        }

        public GameHeader LoadGameHeader(short level, short variant)
        {
            const string commandText = "select * from GameHeader where Level={0} and Variant={1}";
            DataTable headerTable = ReadAllData(String.Format(commandText, level, variant));

            var query = (
                from hd in headerTable.AsEnumerable()
                select new GameHeader
                {
                    Id = hd.Field<int>("id"),
                    Level = hd.Field<short>("Level"),
                    Variant = hd.Field<short>("Variant"),
                    Person = new Point(hd.Field<short>("PersonX"), hd.Field<short>("PersonY")),
                    BestResult = 0
                }).SingleOrDefault();

            return query;
        }

        /// <summary>
        /// Užkrauna konkretaus žaidimo elementus. Metodas kviečiamas iš skirtingų tredų.
        /// </summary>
        /// <param name="gameId">Žaidimo ID</param>
        /// <param name="elementType">Kraunamų elementų tipas, <see cref="VisualType"/></param>
        /// <returns>Nuskaityti elementai</returns>
        public IEnumerable<IGameElement> LoadVisualElements(int gameId, VisualType elementType)
        {
            const string commandText = "select * from GameElements where GameId={0} and ElementType={1}";
            DataTable elementTable;
            var rzlt = new List<IGameElement>();

            lock (this)
            {
                elementTable = ReadAllData(String.Format(commandText, gameId, (short)elementType));
            }
            
            // Lentelėje GameElements ToPointX galima nepildyti. Šio lauko reikšmė tuomet yra lygi PointX.
            // Ta pati taisyklė galioja ir ToPointY laukui: ToPointY == NULL --> PointY
            var tmp =
                from elements in elementTable.AsEnumerable()
                select new
                {
                    GameId = elements.Field<int>("GameId"),
                    ElementType = elements.Field<short>("ElementType"),
                    Xmin = elements.Field<short>("PointX"),
                    Xmax = elements.Field<short?>("ToPointX"),
                    Ymin = elements.Field<short>("PointY"),
                    Ymax = elements.Field<short?>("ToPointY"),
                };
            foreach (var range in tmp)
            {
                short xMax = range.Xmax ?? range.Xmin;
                short yMax = range.Ymax ?? range.Ymin;
                short xMin = range.Xmin, yMin = range.Ymin;

                if (xMin > xMax)  // Sukeisti vietom
                {
                    xMin = xMax;
                    xMax = range.Xmin;
                }
                if (yMin > yMax) // Sukeisti vietom
                {
                    yMin = yMax;
                    yMax = range.Ymin;
                }

                for (short x = xMin; x <= xMax; x++)
                    for (short y = yMin; y <= yMax; y++)
                    {
                        rzlt.Add(new GameElement(x, y, range.ElementType));
                    }
            }

            return rzlt;
        }

        public static GameLevel SafeConvertionToGameLevel(short value)
        {
            try
            {
                return (GameLevel)value;
            }
            catch (Exception)
            {
                return GameLevel.Unassigned;
            }
        }

        #region Private methods

        private DataTable ReadAllData(string commandText)
        {
            Debug.Assert(!_isOpened, "Database is used by another command");

            try
            {

                Conn.Open();
                _isOpened = true;
                DbCommand sql_cmd = Conn.CreateCommand();

                DataAdapter db = new SqlCeDataAdapter(commandText, _conn); // Adapter requires SQLiteConnection
                DataSet ds = new DataSet() { Locale = CultureInfo.InvariantCulture };
                try
                {
                    db.Fill(ds);
#if DEBUG
                    int i = ds.Tables[0].Rows.Count;
#endif
                }
                catch (Exception ex)
                {
                    string msg = ex.Message;
                }

                return ds.Tables[0];
            }
            finally
            {
                Conn.Close();
                _isOpened = false;
            }
        }

        private string GetSqlLighPath()
        {
            string path = System.IO.Path.GetDirectoryName(
                                System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
            if (path.StartsWith("file:"))
                path = path.Substring(6);
            return path;
        }

        #endregion
    }
}
